package cn.langpy.dblistener.core.sqlparse;

import cn.langpy.dblistener.core.model.transform.EventColumn;
import cn.langpy.dblistener.core.model.transform.EventRow;
import cn.langpy.dblistener.core.SqlParser;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;

import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.logging.Logger;

public class MysqlParser implements SqlParser {
    private static Logger log = Logger.getLogger(MysqlParser.class.toString());
    private static List<String> keywords = new ArrayList<>(){
        {
            add("primary");
            add("table");
            add("database");
            add("signal");
            add("create");
            add("update");
            add("insert");
            add("version");
            add("order");
            add("limit");
            add("limit");
            add("left");
            add("right");
            add("join");
        }
    };
    @Override
    public String toInsertSql(String dataBase, String tableName, EventRow rowData) {
        List<EventColumn> afterColumns = rowData.getAfters();
        List<String> columns = new ArrayList<>();
        List<String> values = new ArrayList<>();
        for (int i = 0; i < afterColumns.size(); i++) {
            EventColumn column = afterColumns.get(i);
            String name = column.getName();
            Object value = column.getValue();
            columns.add(keywords.contains(name)?("`"+name+"`"):name);
            String columnComment = column.getComment();
            if (value == null || "".equals(value)) {
                values.add("null");
            } else {
                if (columnComment.equals("string")) {
                    String valueStr = formatValue(value.toString());
                    if (valueStr.startsWith("{") && valueStr.endsWith("}")) {
                        JSONJudge json = isJson(value);
                        if (json.getJson()) {
                            values.add( "'"+formatValue(json.getContent())+"'");
                        }else {
                            values.add("'" + valueStr + "'");
                        }
                    }else {
                        values.add("'" + valueStr + "'");
                    }
                } else if (columnComment.equals("json")) {
                    JSONJudge json = isJson(value);
                    values.add( "'"+formatValue(json.getContent())+"'");
                } else {
                    values.add(value + "");
                }
            }

        }
        if (columns.size()!=values.size()) {
            throw new RuntimeException("Column count doesn't match value count："+rowData);
        }
        String insertSql = "insert into " + dataBase + "." + tableName + "(" + String.join(",", columns) + ")" + " values " + "(" + String.join(",", values) + ")";
        return insertSql;
    }

    private static JSONJudge isJson(Object value) {
        JSONJudge judge = new JSONJudge();
        if (value == null) {
            judge.setJson(false);
            judge.setContent(value.toString());
        }
        try {
            JSONObject parse = JSONObject.parseObject(String.valueOf(value));
            if (parse!=null) {
                judge.setJson(false);
                judge.setContent(JSON.toJSONString(parse));
            }
        } catch (Exception e) {

        }
        return judge;
    }

    private static String formatValue(String value){
        return value
                .replace("'","\\'")
                .replace("\\n","\\\\n")
                .replace("\\r","\\\\r")
                .replace("\\","\\\\")
                .replace("\\\"","\\\\\"");
    }

    @Override
    public String toDeleteSql(String dataBase, String tableName, EventRow rowData) {
        List<EventColumn> befores = rowData.getBefores();
        Object id = null;
        String deleteSql = "";
        for (EventColumn before : befores) {
            if (before.getName().toLowerCase().equals("id")) {
                id = before.getValue();
                break;
            }
        }
        if (id instanceof String) {
            deleteSql = "delete from " + dataBase + "." + tableName + " where id='" + id + "'";
        } else {
            deleteSql = "delete from " + dataBase + "." + tableName + " where id=" + id;
        }
        return deleteSql;
    }

    @Override
    public String toUpdateSql(String dataBase, String tableName, EventRow rowData) {
        List<EventColumn> beforeColumns = rowData.getBefores();
        List<EventColumn> afterColumns = rowData.getAfters();
        String whereSql = " where";
        List<String> sets = new ArrayList<>();
        for (int i = 0; i < afterColumns.size(); i++) {
            EventColumn bcolumn = beforeColumns.get(i);
            String bname = bcolumn.getName();
            Object bvalue = bcolumn.getValue();
            EventColumn acolumn = afterColumns.get(i);
            String aname= acolumn.getName();
            if (keywords.contains(aname.toLowerCase(Locale.ROOT))) {
                aname = "`"+aname+"`";
            }
            Object avalue = acolumn.getValue();
            if ((avalue == null) && (bvalue == null)) {
                continue;
            }
            if (bname.toLowerCase(Locale.ROOT).equals("id")) {
                if (bvalue instanceof String) {
                    whereSql += " id='" + bvalue + "'";
                } else {
                    whereSql = " id=" + bvalue;
                }
            }
            String columnComment = bcolumn.getComment();
            if (columnComment.equals("string")) {
                if (avalue == null) {
                    sets.add(aname + "=null");
                } else if (!avalue.equals(bvalue)) {

                    String valueStr = formatValue(avalue.toString());
                    if (valueStr.startsWith("{") && valueStr.endsWith("}")) {
                        JSONJudge json = isJson(avalue);
                        if (json.getJson()) {
                            sets.add(aname + "='" + formatValue(json.getContent())+"'");
                        }else {
                            sets.add(aname + "=" + "'" + valueStr + "'");
                        }
                    }else {
                        sets.add(aname + "=" + "'" + avalue + "'");
                    }
                }
            } else {
                if (avalue == null) {
                    sets.add(aname + "=" + avalue);
                } else if (!avalue.equals(bvalue)) {
                    sets.add(aname + "=" + avalue);
                }
            }

        }
        String updateSql = "update " + dataBase + "." + tableName + " set " + String.join(",", sets) + whereSql;
        return updateSql;
    }

}

class JSONJudge{
    private String content;
    private Boolean json;

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Boolean getJson() {
        return json;
    }

    public void setJson(Boolean json) {
        this.json = json;
    }
}
